﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text;

namespace ConnectBaseLocal
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {

        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }


        [WebMethod]
        public DataSet ScanMAWB(string mawb_no )
        {
            try
            {

                ////////////////////////   วิธีที่1   ////////////////////////////////////////////////
                //SqlConnection sqlConnection1 = new SqlConnection("Server=WIN-7FORTEST;Database=user_master;User Id=por; Password=1234");
                //SqlCommand cmd = new SqlCommand("Select * from testhht where mawb_no=@mawb_no ", sqlConnection1);
                //DataSet dt = new DataSet();
                //SqlDataAdapter sdata = new SqlDataAdapter();
                //sdata.SelectCommand = cmd ;
                //cmd.Parameters.AddWithValue("@mawb_no", mawb_no);
                //sqlConnection1.Open();

                //cmd.ExecuteNonQuery();
                //sdata.Fill(dt);


                //////////////////////////////วิธีที่2////////////////////////////////////////////////
                //SqlDataAdapter sdata = null;
                //StringBuilder strsql = new StringBuilder();
                //DataTable dt = new DataTable();
                //strsql.Append("Select * from testhht where mawb_no=@mawb_no ");
                //sdata = new SqlDataAdapter(strsql.ToString(), sqlConnection1);
                //cmd = sdata.SelectCommand;
                //var _w = cmd;
                //_w.Parameters.Add("@mawb_no", SqlDbType.NVarChar).Value = mawb_no;
                //DataSet ds = new DataSet();
                //sdata.Fill(ds);
                //sdata = null;
                //return ds;
                //////// วิธีที่3  /////////////////////////////////////
                SqlConnection sqlConnection1 = new SqlConnection("Server=192.168.1.210;Database=TestDB;User Id=sa; Password=p@ssw0rd");
                SqlCommand cmd = new SqlCommand("Select * from testhht where mawb_no=@mawb_no", sqlConnection1);
                cmd.Parameters.AddWithValue("@mawb_no", mawb_no);
                sqlConnection1.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception)
            {

                return null;
            }
         

        }




        [WebMethod]
        public bool Conect(string user , string pass)
        {

            SqlConnection s = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter sdata = null;

            DataTable dt = new DataTable();
            string con = null;
            StringBuilder strsql = new StringBuilder();
            con = "Server=WIN-7FORTEST;Database=user_master;User Id=por; Password=1234";
            s.ConnectionString = con;
            strsql.Append("SELECT * FROM item_user WHERE username=@user AND password=@pass");
            sdata = new SqlDataAdapter(strsql.ToString(), s);
            cmd = sdata.SelectCommand;
            var _w = cmd;
            _w.Parameters.Add("@user", SqlDbType.NChar).Value = user;
            _w.Parameters.Add("@pass", SqlDbType.NChar).Value = pass;
            sdata.Fill(dt);
            sdata = null;
            s.Close();
            s = null;
             
                if (dt.Rows.Count >0)
                {
                    return true;
                }
                else
                {
                    return false;
                }

            
            
        }


        //[WebMethod]
        //public string Scan()
        //{
        //    //strsql.Append("SELECT mawb_no FROM texthht ");
        //}
        [WebMethod]
        public bool Connect_CallSto(string user, string pass)
        {
            SqlConnection sqlConnection1 = new SqlConnection("Server=192.168.1.210;Database=TestDB;User Id=sa; Password=p@ssw0rd");
            SqlCommand cmd = new SqlCommand();

            // Int32 rowsAffected;

            cmd.CommandText = "check_password";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = sqlConnection1;
            cmd.Parameters.AddWithValue("@UserID", user);
            cmd.Parameters.AddWithValue("@Password", pass);
            try
            {



                sqlConnection1.Open();
                SqlDataReader read = cmd.ExecuteReader();
                if (read.Read())
                {

                    //string name = read["Name"].ToString();
                    //string lastname = read["LastName"].ToString();
                    //string Role = read["Role"].ToString();
                    //string wellcome;

                    //wellcome = "Wellcome คุณ " + name + " " + lastname + " ตำแหน่ง " + Role;




                    //MessageBox.Show(wellcome);

                    return true;

                }

                else
                {
                    //string wellcome;

                    //wellcome = "Password or User Fail";




                   // MessageBox.Show(wellcome);
                    return false;
                }


            }

            catch (Exception objEx)
            {
                //string str = objEx.Message;
               // MessageBox.Show(str);
                return false;
            }

        }
    }
}
